if OBJECT_ID('print_string_in_lines', 'P') > 0 drop procedure print_string_in_lines
go


create procedure dbo.print_string_in_lines(
	@s nvarchar(max), -- on SQL Server 2005 use ntext
	@verbose int = 0
) as
begin
-- part of Extreme T-SQL Script http://etsql.codeplex.com/
-- by Bernd Kriszio https://twitter.com/SQLSulidae  2010-10-31
-- requires SQL Server 2008: varchar(max).
-- adaption to SQL Server 2005 possible: see hints

-- this is a helper to print long strings. 
-- Input Strings must have moderate page_width < 4000,
-- restriction in number of lines are not explored

	--print '-- dl: ' + str(datalength(@s))
	declare @crnl char(2) = CHAR(13) + CHAR(10)
	declare @pos int

	-- never believe lineendings to be consistent, perhaps someone even has a MAC
	-- for SQL-Server 2005 remove next line
	set @s = replace(	replace(@s, @crnl, CHAR(10))  , char(13), CHAR(10)  )
	while (Datalength(@s) > 0)
	begin
		set @pos = charindex(CHAR(10), @s)
		if @pos > 0
		begin
			if @verbose > 0
				print datalength(left (@s, @pos - 1) )
			print left (@s, @pos - 1)  -- SQL 2005: @pos - 2
			set @s = SUBSTRING(@s, @pos + 1, len(@s))
		end
		else
		begin
			print @s
			set @s = ''
		end
	end				
end
go
